package com.pai4j.common.util.file;
import java.io.IOException;
import java.io.InputStream;
import java.io.OutputStream;
import java.net.HttpURLConnection;
import java.net.URL;
import java.util.*;

/**
 * excel工具类
 *
 */
public class ExcelUtils {

    /**
     * 导出excel
     *
     * @param os        :输出流
     * @param sheetList ：sheet值
     * @param columnMap ：数据
     *
     * @throws IOException
     * @throws WriteException
     * @throws RowsExceededException
     */
    public static void exportExcel(OutputStream os, List<Map<String, Object>> sheetList,
                                   Map<String, List<Map<String, String>>> columnMap)
            throws IOException, RowsExceededException, WriteException {
        // 建立excel文件
        WritableWorkbook workbook = Workbook.createWorkbook(os);
        for (int i = 0; i < sheetList.size(); i++) {
            Map<String, Object> sheetMap = sheetList.get(i);
            String sheetKey = (String) sheetMap.get("key");
            String sheetName = (String) sheetMap.get("name");
            // 工作表名称
            WritableSheet sheet = workbook.createSheet(sheetName, i);

            List<Map<String, String>> columnList = columnMap.get(sheetKey);
            // 写title
            writeTitle(sheet, columnList);
            // 写数据
            List<Map<String, String>> valueList = null;
            if (sheetMap.get("value") != null) {
                valueList = (List<Map<String, String>>) sheetMap.get("value");
            }
            writeData(sheet, valueList, columnList);
        }
        // 写入文件
        workbook.write();
        workbook.close();
    }

    /**
     * 输出title
     *
     * @param sheet
     * @param columnList
     *
     * @throws WriteException
     * @throws RowsExceededException
     */
    private static void writeTitle(WritableSheet sheet, List<Map<String, String>> columnList)
            throws RowsExceededException, WriteException {
        // 设置Excel Title字体
        WritableFont wfont = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD, false,
                jxl.format.UnderlineStyle.NO_UNDERLINE,
                jxl.format.Colour.BLACK);
        WritableCellFormat titleFormat = new WritableCellFormat(wfont);
        // 设置Excel表头
        for (int i = 0; i < columnList.size(); i++) {
            Map<String, String> columnMap = columnList.get(i);
            Label excelTitle = new Label(i, 0, columnMap.get("title"), titleFormat);
            sheet.addCell(excelTitle);
        }
    }

    /**
     * 输出数据
     *
     * @param sheet
     * @param valueList
     * @param columnList
     *
     * @throws RowsExceededException
     * @throws WriteException
     */
    private static void writeData(WritableSheet sheet, List<Map<String, String>> valueList,
                                  List<Map<String, String>> columnList)
            throws RowsExceededException, WriteException {
        if (valueList != null) {
            for (int i = 0; i < valueList.size(); i++) {
                Map<String, String> valueMap = valueList.get(i);
                for (int j = 0; j < columnList.size(); j++) {
                    Label data = new Label(j, i + 1, valueMap.get(columnList.get(j).get("key")));
                    sheet.addCell(data);
                }
            }
        }
    }

    /**
     * 导入
     *
     * @param columnList
     * @param remoteHost
     *
     * @return
     *
     * @throws IOException
     */
    public static List<Map<String, String>> readExcel(List<Map<String, String>> columnList, String fileUrl,
                                                      String remoteHost) throws IOException {
        // 集群环境，拼装相对主机名的URL
        fileUrl = FileUtil.getFileURL(fileUrl, remoteHost);
        if (StringUtils.isNotBlank(fileUrl)) {
            return readExcel(columnList, fileUrl);
        }

        return Collections.emptyList();
    }

    public static List<Map<String, String>> readExcel(List<Map<String, String>> columnList, String fileUrl) {
        List<Map<String, String>> valueList = Collections.emptyList();
        // 获取网络输入流
        InputStream is;
        if (StringUtils.isNotBlank(fileUrl)) {
            HttpURLConnection urlConnection = null;
            valueList = new ArrayList<>();

            try {
                URL url = new URL(fileUrl);
                urlConnection = (HttpURLConnection) url.openConnection();
                // 连接指定的资源
                urlConnection.connect();

                is = urlConnection.getInputStream();
                Workbook book = Workbook.getWorkbook(is);
                Sheet sheet = book.getSheet(0);
                int rows = sheet.getRows();
                for (int i = 1; i < rows; i++) {
                    Map<String, String> valueMap = new HashMap<>();
                    for (int j = 0; j < columnList.size(); j++) {
                        Map<String, String> column = columnList.get(j);
                        String key = column.get("key");
                        Cell cell = sheet.getCell(j, i);
                        String value = cell.getContents();
                        if (value != null && value.length() > 0) {
                            valueMap.put(key, value);
                        }
                    }
                    valueList.add(valueMap);
                }

                book.close();
            } catch (Exception e) {
                e.printStackTrace();
                throw new BusinessException("文件读取错误!");
            } finally {
                if (urlConnection != null) {
                    urlConnection.disconnect();
                }
            }
        }

        return valueList;
    }

    /**
     * 根据数据制作Excel
     *
     * @param list
     * @param width
     *
     * @return
     *
     * @throws SecurityException
     * @throws IllegalArgumentException
     */
    public static HSSFWorkbook createExcel(List<List<String>> list, int[]... width)
            throws SecurityException, IllegalArgumentException {
        // 创建文件
        HSSFWorkbook workbook = new HSSFWorkbook();

        // 创建工作表
        HSSFSheet sheet = workbook.createSheet("Sheet1");

        // 列宽度
        if (width.length > 0) {
            for (int i = 0; i < width[0].length; i++) {
                sheet.setColumnWidth(i, width[0][i]);
            }
        }

        // 创建单元格的格式，如居中、左对齐等
        HSSFCellStyle cellStyle = workbook.createCellStyle();
        // 水平方向上居中对齐
        cellStyle.setAlignment(HSSFCellStyle.ALIGN_CENTER);
        // 垂直方向上居中对齐
        cellStyle.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);

        // 标题行
        for (int i = 0; i < list.size(); i++) {
            List<String> items = list.get(i);
            HSSFRow row = sheet.createRow(i);
            for (int j = 0; j < items.size(); j++) {
                HSSFCell cell = row.createCell(j);
                cell.setCellValue(new HSSFRichTextString(items.get(j)));
                cell.setCellStyle(cellStyle);
            }
        }

        return workbook;
    }
}
